#!/bin/perl

# This program determines a ranking of all dragon boat teams.
# It requires that all dragon boat races be stored in a database called
# 'dboat' in a table called 'race'.  It also expects a table called regattas
# which it uses to determine which regattas to include in the ranking and the
# order that they will be regressed.  Regatta #0 is the base regatta (currently
# I am using Center Island).  This means after the regressions the ranking time
# is how fast a team would be if it raced at Center Island.
#
# This program interacts with the dboat DB2 database using the DBI interface
# it creates intermediate tables and ultimately stores the ranking in the 
# trueRank table.
# 
# ------------------------------------------------------------------------------

use DBI;
use DBD::DB2::Constants;
use DBD::DB2;

use Statistics::Regression;
use Statistics::Descriptive;

use Algorithm::CurveFit;

use POSIX qw(ceil floor);


my $rankingsYear = 2013;
my $lastRankingsYear = $rankingsYear - 1;

my $sqlcode = 0;      
my $commonTeams = 0;   

# We can set $gender if we want to filter some genders from the final
# printing of results.  They will still be used in the regression. 
# specify the genders we want to keep.
# -----------------------------------------------------------------------------
#my $gender = "('Women', 'Mixed')";     
#my $gender = "('Women', 'Mixed', 'Open')";
my $gender = "('Open')";
#my $gender = "('Women')";
#my $gender = "('Mixed')";

# Select what distance we will consider.  If useAllDistances is not
# set then we can specify one particular distance.  Otherwise we will
# incorporate all race distances into the rankings.
# -----------------------------------------------------------------------------
my $useAllDistances = 0;
my $distance = 500;
#my $distance = 200;
#my $distance = 2000;     
     

# We don't actually have to use regression.  We could for example look at all the teams
# that did Center Island and Pickering and find out as a % how much 
# slower was Pickering on average for these teams.  Then adjust each team by this percentage.  this
# Would basically be a regression on 1 variable where we force the intercept to be
# zero.  The regression functions require at least 2 variables so I'd need to write
# my own function to do this simple calculation
# -----------------------------------------------------------------------------
my $forceZeroIntercept = 0;

# Another algorithm is to regress the adjustment itself so it is a function of
# how fast the team goes.
# -----------------------------------------------------------------------------
my $regressAdjustment = 1;

# Now if I want to be really crazy instead of a linear regression of the adjustment
# versus boat speed I can do a non-linear curve fit.  This way we could for example
# adjust slow and fast teams differently than medium teams
# -----------------------------------------------------------------------------
my $nonLinearCurveFit = 1; 
      
# arrays to hold the values I will be regressing     
my @regRegatta = ();        # the actual time for a team
my @regTrueRank = ();       # the adjusted time for a team
my @regAdjustment = ();     # the implied adjustment for a team
my @regCalcAdjustment = (); # the actual adjustment used

# The order that we print regattas will be different than the order that we add 
# them to the regression
# -----------------------------------------------------------------------------
my @printOrder = ();


# To help evaluate the quality of the regression I will output the data to a file
# in a format that I can easy work with in MATLAB for visualization.  This file
# will not be used by the automated regression other than as a log
# -----------------------------------------------------------------------------
my $matlabLog = "C:\\DragonBoatRankings\\matlab.txt";
my $outFile = "$matlabLog";
open(F, ">$outFile") || die("Could not open $outFile");
  
     
# Open connection to dboat
# ------------------------------------------------------------------------------ 
my $dbh = DBI->connect("dbi:DB2:dboat", "", "");
if (!defined($dbh)) 
{ 
   print("database handle, dbh not defined\n");
   exit; 
}


# Store the regattas in a hash
# ------------------------------------------------------------------------------
my $stmt = "select NAME,REGRESS_ORDER,PRINT_ORDER,COMPETITIVE from regattas where REGRESS_ORDER >= 0";
my $sth = $dbh->prepare($stmt);
$sth->execute();

# Associate variables with output columns...
my $i = 0;
my @regattas;
my $col1;
my $col2;
my $col3;
my $col4;
$sth->bind_col(1,\$col1);
$sth->bind_col(2,\$col2);
$sth->bind_col(3,\$col3);
$sth->bind_col(4,\$col4);

while ($sth->fetch) 
{ 
   $regattas[$col2] = $col1;    #regattas[3] contains the name of the 3rd regatta to regress
   $competitve[$col2] = $col4;  #competitive[3] contains the weighted average multipler for regattas[3]
   $printOrder[$col3] = $col2;  #printOrder[3] contains the regattas index of the 3rd regatta to print
}
$sth->finish();


# Create trueRank table which will store each team's ranking time
# This table will be updated each time a new regatta is regressed
# -----------------------------------------------------------------------------
$stmt = "drop table trueRank";
$sth = $dbh->prepare($stmt);
$sth->execute();  
$stmt = "create table trueRank (TEAM VARCHAR(100), TIME DECIMAL(31,20), NUMREGATTAS INTEGER)";
$sth = $dbh->prepare($stmt);
$sth->execute();   
$sth->finish();

# populate trueRank with 1 entry for every team we have a record of in RACE table.  
# For now make the ranking time = 0.  This will be adjusted for each regatta 
# the team attends.
# -----------------------------------------------------------------------------  
$stmt = "insert into truerank select unique team, 0.0, 0 from race";
$sth = $dbh->prepare($stmt);
$sth->execute(); 
$sth->finish();



# Main Loop
# For every regatta stored in the database we will do the same thing:
# (1) Create a new table containing each team that participated in the regatta and their
#     Average time at the regatta
# (2) Determine which teams in this new regatta already are ranked in the true Rankings
# (3) Use the teams in common to determine how to adjust the regatta times.  If all of the
#     teams went slower then we need to adjust the times to make them faster to be comparable.
#     We achieve this by performing a regression between the trueRank times and new regatta times
# (4) Adjust all of the teams in the new regatta based on step #3
# (5) Average this new adjusted time into the trueRankings
# -----------------------------------------------------------------------------
$i = 0;
for ($i=0; $i<=$#regattas; $i++)
{
   print("regatta $i:  $regattas[$i] \n");
   
   # Create a table for each regatta
   # -------------------------------------------------------------------------------
   $stmt = "drop table regatta$i";
   $sth = $dbh->prepare($stmt);
   $sth->execute();
   $sth->finish();
     
   $stmt = "create table regatta$i (TEAM VARCHAR(100), AVGTIME DECIMAL(31,4), ADJAVGTIME DECIMAL(31,20), NUMRACES INTEGER, EXCLUDE_FLAG INTEGER)";
   $sth = $dbh->prepare($stmt);
   $sth->execute();
   $sth->finish();   
   
   

   # Incorporate all race distances.  Previously we took only the average 500m time and
   # stored it in the $regatta$i table.  We want to look at all distances and adjust
   # them to 500m times.  We will make either behavior possible. By setting the
   # $useAllDistances variable to 1 we will include all race distances in the 
   # rankings.
   # ----------------------------------------------------------------------------
   $stmt = "select distinct(distance) from race where regatta like '$regattas[$i]' ";
   $sth = $dbh->prepare($stmt);
   $sth->execute();
   $sth->bind_col(1,\$col1);

   my @skipDistance = ();   
   my @distances = ();
   my $numDistances=0;
   while ($sth->fetch) 
   {
      $skipDistance[$numDistances] = 0; 
      $distances[$numDistances++] = $col1;    #$distances[0] contains the first distance other than 500m       
   }
   $sth->finish();
   
   # If the regatta had only one race distance then there is no need to adjust all times
   # to a common distance.  Otherwise adjust everything to 500m times (or another distance
   # if there was no 500m time).  First we handle the case where there is only 
   # one distance to deal with.
   # --------------------------------------------------------------------------
   if ($#distances == 0 || $useAllDistances!=1)
   {
      my $distanceString = "";
      $distanceString = "distance=$distance and" if ($useAllDistances!=1);
      
      # Populate table with average race time for each team
      # --------------------------------------------------------------------------
      $stmt = "insert into regatta$i select TEAM, AVG(TIME), AVG(TIME), COUNT(TIME), MAX(EXCLUDE_FLAG) from race " .
              "where $distanceString regatta like '$regattas[$i]' " .
              #"and category in $gender " .
              "group by team order by AVG(TIME) ";
      $sth = $dbh->prepare($stmt);
      $sth->execute();
      $sth->finish();
   } # Finished getting average time for regatta with only 1 distnace
   else
   {
      # This is the case where we are including all distances in the rankings
      # and the regatta we are currently incorporating into the rankings has
      # more than one distance
      # -----------------------------------------------------------------------
      $stmt = "insert into regatta$i select distinct(TEAM), 0, 0, 0, 0 from race " .
              "where regatta like '$regattas[$i]' ";

      $sth = $dbh->prepare($stmt);
      $sth->execute();
      $sth->finish();
      
      my $distance = 0;
      my $base = 0;
      my $baseTable = ""; 
      # create table for each distance
      for (my $distanceIterator=0; $distanceIterator<$numDistances; $distanceIterator++)
      {
         my $tableName = "regatta${i}_" . $distances[$distanceIterator];
         $distance = $distances[$distanceIterator];
         
         $base = $distanceIterator if ($distance==500);
         
         $stmt = "drop table $tableName";
         $sth = $dbh->prepare($stmt);
         $sth->execute();
         $sth->finish();
     
         $stmt = "create table $tableName (TEAM VARCHAR(100), AVGTIME DECIMAL(31,4), ADJAVGTIME DECIMAL(31,20), NUMRACES INTEGER)";
         print("create table statement is: $stmt \n");
         $sth = $dbh->prepare($stmt);
         $sth->execute();
         $sth->finish();   
    
         # Populate table with average race time for each team for that distance
         # --------------------------------------------------------------------------
         $stmt = "insert into $tableName select TEAM, AVG(TIME), AVG(TIME), COUNT(TIME) from race " .
                 "where distance=$distance and regatta like '$regattas[$i]' " .
                 #"and category in $gender " .
                 "group by team order by AVG(TIME) ";
         print("populate table statement is $stmt \n");
         $sth = $dbh->prepare($stmt);
         $sth->execute();
         $sth->finish();      
      } # finished creating tables for each distance
      
      $baseTable = "regatta${i}_" . $distances[$base];
      print("baseTable is $baseTable\n");
      for (my $distanceIterator=0; $distanceIterator<$numDistances; $distanceIterator++)
      {
         # The base distance (500 if it exists) is what we adjust all times to. If
         # This is the base distance then we have no adjustment to make so we skip
         # it and go on to the next distance
         # --------------------------------------------------------------------
         next if ($distanceIterator==$base);
         
         $distance = $distances[$distanceIterator];
         
         my $adjustingTable = "regatta${i}_" . $distances[$distanceIterator];
         print("adjustingTable is $adjustingTable \n");    
         # Remember it is not necessary that the same teams performed each distance
         # so only consider those teams that did perform both distances in the regression
         # ie teams in $baseTable and $adjustingTable
         # --------------------------------------------------------------------
         my $cutoff = 157.5;
         if ($distance <= 200) 
         {  
            $cutoff = 75  
         }
         elsif ($distance <=500)
         {
            $cutoff = 160;
         }
         elsif ($distance <= 2000)
         {
            $cutoff = 900;
         }
         
         $stmt = "select $baseTable.TEAM, $baseTable.AVGTIME, $adjustingTable.AVGTIME from $baseTable, $adjustingTable where $baseTable.TEAM = $adjustingTable.TEAM and $baseTable.AVGTIME > 0 and $adjustingTable.AVGTIME < $cutoff";
         print("query to get common teams is $stmt \n");
         $sth = $dbh->prepare($stmt);
         $sth->execute();
      
         $sth->bind_col(1,\$col1);
         $sth->bind_col(2,\$col2);
         $sth->bind_col(3,\$col3);
         print("Teams in common between base table $baseTable and adjusting table and $adjustingTable: \n");
         print("Team, $baseTable time, $adjustingTable time, implied Adjustment \n");
         print F "\n\n\n$regattas[$i]" . "_$distance = [\n";
         $commonTeams=0;
         @regDistance = ();     
         @regBase = ();      
         @regAdjustment = ();    
         @regCalcAdjustment = ();
         while ($sth->fetch) 
         { 
            print "$col1, $col2, $col3, " . $col2/$col3 . " \n";
            print F "$col3 " . $col2/$col3 . ";\n";
            $regBase[$commonTeams] = $col2;  # X = Base time (usually 500m time)
            $regDistance[$commonTeams] = $col3;   # Y = special distance time (eg 200m or 2000m)
            $regAdjustment[$commonTeams] = $col2 / $col3; # what you need to multiply this distance by to normalize
            $commonTeams++; 
         }
         $sth->finish();
         print F "]\n\n";
         print("$commonTeams teams in common between $baseTable and $adjustingTable  \n");
         
         if ($commonTeams < 8)
         {
            $skipDistance[$distanceIterator] = 1;
            print("skipping this distance $adjustingTable\n");
            next;
         }
          
         # By this point we have all the:
         # 500m times in $regBase[]
         # 200m times in $regDistance[]
         # adjustment to multiply 200m times by in $regAdjustment 
         # Now we wish to find the relationship between the 500m and 200m times by performing
         # a regression
         # --------------------------------------------------------------------
         $formula = 'a + b * x + c * x^2';
               
         my $variable = 'x';
  
         my $aGuess = 1;
         my $bGuess = 0;
         my $cGuess = -1;
     
         my @parameters = ();
               
         @parameters = (
                               # Name    Guess   Accuracy
                               ['a',     $aGuess,    0.0000001],  # If an iteration introduces smaller
                               ['b',     $bGuess,    0.0000001],  # changes than the accuracy, end.
                               ['c',     $cGuess,    0.0000001], 
                       );
              
                                
         my $max_iter = 1000; # maximum iterations
  
         my $square_residual = Algorithm::CurveFit->curve_fit(
                                                              formula => $formula, 
                                                              params  => \@parameters,
                                                              variable=> $variable,
                                                              xdata   => \@regDistance,
                                                              ydata   => \@regAdjustment,
                                                              maximum_iterations => $max_iter,
                                                              );
               
         print(" $parameters[0][0] $parameters[0][1] $parameters[0][2] \n");
         print(" $parameters[1][0] $parameters[1][1] $parameters[1][2] \n");
         print(" $parameters[2][0] $parameters[2][1] $parameters[2][2] \n");
     
         my $a = $parameters[0][1];
         my $b = $parameters[1][1];
         my $c = $parameters[2][1];
       
         my $scientificNotation;
         $scientificNotation = "";
         if ($a =~ m/(e\-\d+)/)
         {
            $scientificNotation = $1;
         }
         if ($a =~ m/(\-?\d+\.\d{10})/)
         {
            $a = "$1"."$scientificNotation";
         }
         $scientificNotation = "";
         if ($b =~ m/(e\-\d+)/)
         {
            $scientificNotation = $1;
         }
         if ($b =~ m/(\-?\d+\.\d{10})/)
         {
            $b = "$1"."$scientificNotation";
         }
         $scientificNotation = "";
         if ($c =~ m/(e\-\d+)/)
         {
            $scientificNotation = $1;
         }
         if ($c =~ m/(\-?\d+\.\d{10})/)
         {
            $c = "$1"."$scientificNotation";
         }
        
       
         print("Adjustment = $a +  $b * $adjustingTable  + $c * ($adjustingTable)^2  \n"); 
                                       
         print("Other Distance Time, implied adjustment, adjustment used, new time \n");
         my $statY = Statistics::Descriptive::Full->new();    
         for (my $k=0; $k<$commonTeams; $k++)
         {    
            $regCalcAdjustment[$k] = $a + $b*$regDistance[$k] + $c*$regDistance[$k]*$regDistance[$k];
                              
            print("$regDistance[$k],  $regAdjustment[$k], $regCalcAdjustment[$k], " . $regDistance[$k]*$regCalcAdjustment[$k] . " \n");
            $statY->add_data($regCalcAdjustment[$k]);       
         }
  
         my $maxInSampleAdjust = $statY->max();
         my $minInSampleAdjust = $statY->min();
         
         # Print out MATLAB comments for us to visualize the adjustment curve
         # used to move heats of one distance to the base distance (usually 500m)
         # --------------------------------------------------------------------     
         print F "clear X Y H domain Yhat Adjustment hData\n";               
         print F "X=${regattas[$i]}_$distance(:,1);\n";
         print F "Y=${regattas[$i]}_$distance(:,2);\n";
         print F "H=scatter(X,Y);\n";
         print F "hTitle  = title('${regattas[$i]}_$distance');\n";
         print F "domain = min(X):1:max(X)\n";
         #if ($useCubic)
         #{
         #   print F "Adjustment = $a +  $b.*domain   + $c.*domain.*domain + $d.*domain.*domain.*domain\n";
         #}
         #else
         #{
         print F "Adjustment = $a +  $b.*domain   + $c.*domain.*domain \n";
         #}
         print F "hold on \n";
         print F "for i=1:length(Adjustment)\n";  
         print F "   Yhat(i) = max(Adjustment(i), $minInSampleAdjust); \n";
         print F "   Yhat(i) = min(Adjustment(i), $maxInSampleAdjust); \n";
         print F "end\n";
         print F "hData = line(domain, Yhat)\n";
               
                
         # insert new adjusted time into $adjustedTable
         # --------------------------------------------------------------------
         $stmt = "update $adjustingTable set ADJAVGTIME = AVGTIME * MIN($maxInSampleAdjust, MAX($minInSampleAdjust, ($a + $b*AVGTIME + $c*AVGTIME*AVGTIME)))";
               
         print("Adjust time via: $stmt\n");
         $sth = $dbh->prepare($stmt);
         $sth->execute();
         $sth->finish();     
      }
      
      
      # Take the appropriate weighted average of all the different distance adjusted times
      # and set this as the base time.  To do this we will loop over each distance and add
      # (adusted distance time * #races at that distance) to a total and divide by the total
      # number of races
      # -----------------------------------------------------------------------
      for (my $distanceIterator=0; $distanceIterator<$numDistances; $distanceIterator++)
      { 
         my $incomingTable = "regatta${i}_" . $distances[$distanceIterator];
         print("Adding in $incomingTable \n");
         
         if ($skipDistance[$distanceIterator])
         {
            print("skipping " . $distances[$distanceIterator] . " from regatta$i update\n");  
            if (0) 
            {
            $stmt = "update regatta$i set regatta$i.NUMRACES = regatta$i.NUMRACES - " .
                 "(select $incomingTable.NUMRACES from $incomingTable where $incomingTable.TEAM=regatta$i.TEAM)";
            print("updating numraces via: $stmt\n");
            $sth = $dbh->prepare($stmt);
            $sth->execute();
            $sth->finish();
            }
            next;
         }
             
        
         $stmt = "update regatta$i set AVGTIME = AVGTIME + " .
                 "(select $incomingTable.ADJAVGTIME * $incomingTable.NUMRACES from $incomingTable where $incomingTable.TEAM=regatta$i.TEAM and $incomingTable.ADJAVGTIME > 0)" .
                 "where exists " .
                 "(select $incomingTable.ADJAVGTIME * $incomingTable.NUMRACES from $incomingTable where $incomingTable.TEAM=regatta$i.TEAM and $incomingTable.ADJAVGTIME > 0)";
         print("updating AVGTIME via: $stmt\n");
         $sth = $dbh->prepare($stmt);
         $sth->execute();
         $sth->finish();
         
         $stmt = "update regatta$i set NUMRACES = NUMRACES + " .
                 "(select $incomingTable.NUMRACES from $incomingTable where $incomingTable.TEAM=regatta$i.TEAM and $incomingTable.NUMRACES > 0)" .                 
                 "where exists " .
                 "(select $incomingTable.NUMRACES from $incomingTable where $incomingTable.TEAM=regatta$i.TEAM and $incomingTable.NUMRACES > 0)";                 
         print("updating NUMRACES via: $stmt\n");
         $sth = $dbh->prepare($stmt);
         $sth->execute();
         $sth->finish();
      
      # update regatta$i AVGTIME to $regatta$i.AVGTIME + distanceTable.COUNT(TIME)*distanceTable.ADJAVGTIME
      }
      
      # if we skipped some distances there could be teams with zero races.  Remove these
      $stmt = "delete from regatta$i where NUMRACES = 0";      
      $sth = $dbh->prepare($stmt);
      $sth->execute();
      $sth->finish();
      
      # update regatta$i AVGTIME to AVGTIME/NUMRACES
      $stmt = "update regatta$i set AVGTIME = AVGTIME / NUMRACES";      
      $sth = $dbh->prepare($stmt);
      $sth->execute();
      $sth->finish();
        
   }  # Finished getting AVGTIME for regatta with multiple distances
   
   # END OF ADJUSTING HEATS OF ALL DISTANCES TO BASE DISTANCE FOR A REGATTA
   #============================================================================
   
   # If this is the first regatta we encounter then the trueRank for each team is simply 
   # the average time the team achieved in the 1 regatta.  No regression necessary
   # --------------------------------------------------------------------------
   if ($i == 0)
   {  
      # Increment numregattas count in the trueRank DB for each team that raced
      # in the first regatta
      # -----------------------------------------------------------------------
      $stmt = "UPDATE truerank set truerank.numregattas = truerank.numregattas + 1 where exists (select regatta$i.team from regatta$i where regatta$i.team = trueRank.team)";
      $sth = $dbh->prepare($stmt);
      $sth->execute();
      $sth->finish();
      
      # update the trueRank table setting the trueRank time for each team that
      # raced in the first regatta
      # -----------------------------------------------------------------------
      $stmt = "UPDATE trueRank set trueRank.time = (select regatta$i.ADJAVGTIME from regatta$i where regatta$i.TEAM=trueRank.TEAM) + trueRank.time";
      $sth = $dbh->prepare($stmt);
      $sth->execute();
      $sth->finish();
   }
   # If this is something other than the first regatta then we want to regress this 
   # regattas times against existing truerank times.  We don't want to simply average
   # the times because one regatta may have much faster conditions than another regatta.
   # For teams that only went to the fast regatta this would be an unfair advantage.
   # Therefore we will adjust all times to be on par with the first regatta
   # --------------------------------------------------------------------------
   elsif ($i > 0)
   {
      # we need the teams in common between truerank and this regatta and their average times.
      # By looking at the teams that attended both regattas we will know what time adjustment 
      # to make.  For example if all teams went faster at GWN then we need to adjust the GWN
      # times downward to make the comparable to the teams that didn't race at GWN.
      # 
      # After looking at the regression I decided to exclude teams slower than 2:40.  There was
      # only 1 case but it seems a really slow time may be the result of an accident or just a team
      # so bad that they are too variable.  They were a major outlier in the data points
      # -----------------------------------------------------------------------
      my $cutoff = 157.5;
      $cutoff = 75 if ($distance == 200);
      $cutoff = 900 if ($distance == 2000);
      $stmt = "select trueRank.TEAM, trueRank.TIME, regatta$i.AVGTIME from regatta$i, trueRank where trueRank.TEAM = regatta$i.TEAM and trueRank.TIME > 0 and regatta$i.AVGTIME < $cutoff and regatta$i.EXCLUDE_FLAG = 0";
      #$stmt = "select trueRank.TEAM, trueRank.TIME, regatta$i.AVGTIME from regatta$i, trueRank where trueRank.TEAM = regatta$i.TEAM and trueRank.TIME > 0 order by regatta$i.AVGTIME";
      $sth = $dbh->prepare($stmt);
      $sth->execute();
      
      $sth->bind_col(1,\$col1);
      $sth->bind_col(2,\$col2);
      $sth->bind_col(3,\$col3);
      print("Teams in common between true rank and $regattas[$i]: \n");
      print("Team, true rank time, $regattas[$i] time, implied Adjustment \n");
      print F "\n\n\n$regattas[$i] = [\n";
      $commonTeams=0;
      @regRegatta = ();     
      @regTrueRank = ();      
      @regAdjustment = ();    
      @regCalcAdjustment = ();
      while ($sth->fetch) 
      { 
         print "$col1, $col2, $col3, " . $col2/$col3 . " \n";
         print F "$col3 " . $col2/$col3 . ";\n";
         $regTrueRank[$commonTeams] = $col2;  # X = trueRank time
         $regRegatta[$commonTeams] = $col3;   # Y = this regatta time
         $regAdjustment[$commonTeams] = $col2 / $col3; # what you need to multiply this regatta by to normalize
         $commonTeams++; 
      }
      $sth->finish();
      print F "]\n\n";
      print("$commonTeams teams in common between trueRank and regatta$i ($regattas[$i])  \n");
            
      if (!$forceZeroIntercept)
      {
         # I also provide two different techniques here.  First my original method, 
         # regress 2 races and adjust based on that regression.  I don't plan on
         # going back to this but I'll leave the code here
         # --------------------------------------------------------------------         
         if (! $regressAdjustment)
         {            
            # Setup regression
            my $reg = Statistics::Regression->new( "$regattas[$i]", [ "C", "trueRank" ] );

            # Add data points
            for (my $j=0; $j<$commonTeams; $j++)
            {
              $reg->include( $regRegatta[$j], [1.0, $regTrueRank[$j]] );
            }

            # Perform regression
            $reg->print();
      
            my @theta  = $reg->theta();
            my @se     = $reg->standarderrors();
            my $rsq    = $reg->rsq();
            my $adjrsq = $reg->adjrsq();
            my $ybar   = $reg->ybar();     ## the average of the y vector
            my $sst    = $reg->sst();      ## the sum-squares-total
            my $sigmasq= $reg->sigmasq();  ## the variance of the residual
            my $k      = $reg->k();        ## the number of variables
            my $n      = $reg->n();        ## the number of observations
      
            #eg  Pickering =  $theta[0]+ $theta[1]*CenterIsland  
            print("$regattas[$i]= $theta[0] + trueRank  * $theta[1] \n");
      
            # Set the adjusted times on regatta$i
            $stmt = "update regatta$i set ADJAVGTIME = ((AVGTIME - $theta[0]) / $theta[1])";
            $sth = $dbh->prepare($stmt);
            $sth->execute();
            $sth->finish();
         }
         else  # $regressAdjustment == 1
         {
            # This is the case where we use Power Demons method.  We regress the base
            # regatta's times against the %change (relative to base regatta) in the new regatta.  
            # This allows us to adjust times based on how fast a team is.  A slow team could be 
            # adjusted more or less than a fast team.
            # -----------------------------------------------------------------
            print("Power Demon's method of regressing adjustment\n");
            
            
            # Here we can either use linear regression or non-linear curve fitting.
            # non-linear I originally thought was unintuitive but it actually seems
            # to yield good results and it is what Power Demon used.  With this it is
            # possible to adjust fast teams completely differently (even in opposite direction)
            # than slow teams.  The first branch will be for linear regression which I 
            # can set above.  I also don't allow non-linear curve fitting if there are less
            # than 10 teams because this would likely over fit the data.  In fact we should
            # probably avoid including regattas altogether that have fewer than 10 teams
            # in common.
            # -----------------------------------------------------------------
            if (!$nonLinearCurveFit || $commonTeams < 10) # || $regattas[$i] eq "Woodstock")
            {
               print("Linear regression on adjustments\n");
               
               # X-axis will be Regatta time
               # Y-axis will be Adjustment (ie 0.98 means Pickering time * 0.98 = CI time
               #                            ie this team went 2% slower at Pickering
             
               # Setup regression
               my $reg = Statistics::Regression->new( "Adjustment", [ "C", "$regattas[$i]" ] );

               # Add data points
               for (my $j=0; $j<$commonTeams; $j++)
               {
                 $reg->include( $regAdjustment[$j], [1.0, $regRegatta[$j]] );
               } 

               # Perform regression
               $reg->print();
      
               my @theta  = $reg->theta();
             
               # The adjustment is what we multiply by an individual regatta to noramlize it
               # to the base regatta.  We are going to let the adjustment be different for teams
               # of different speeds.  Our regression determines what the adjustment should be
               # based on the speed of the team
               #   
               # eg  Adjustment =  $theta[0]+ $theta[1]*Pickering
               # -----------------------------------------------------------------  
               print("Adjustment = $theta[0] + $regattas[$i] * $theta[1] \n");
               print("close to 0 slope indicates similar adjustment for all teams " .
                     "Positive slope indicates bigger adjustment for slower teams \n");
                     
               print("regattaTime, implied adjustment, adjustment used, new time \n");
               my $statY = Statistics::Descriptive::Full->new();    
               for (my $k=0; $k<$commonTeams; $k++)
               {
                  $regCalcAdjustment[$k] = $theta[0]+ $theta[1]*$regRegatta[$k];
                  $statY->add_data($regCalcAdjustment[$k]);
                  print("$regRegatta[$k],  $regAdjustment[$k], $regCalcAdjustment[$k], " . $regRegatta[$k]*$regCalcAdjustment[$k] . " \n"); 
               } 
               
               my $maxInSampleAdjust = $statY->max();
               my $minInSampleAdjust = $statY->min();

               # Print MATLAB commands to log file to visualize the regreesion
               # --------------------------------------------------------------
               print F "clear X Y H domain Yhat Adjustment hData\n";
               print F "X=${regattas[$i]}(:,1);\n";
               print F "Y=${regattas[$i]}(:,2);\n";
               print F "H=scatter(X,Y);\n";
               print F "hTitle  = title('${regattas[$i]}');\n";
               print F "domain = min(X):1:max(X)\n";
               print F "Adjustment = $theta[0] + $theta[1].*domain \n";
               print F "hold on \n";
               print F "for i=1:length(Adjustment)\n"; 
               print F "   Yhat(i) = max(Adjustment(i), $minInSampleAdjust); \n";
               print F "   Yhat(i) = min(Adjustment(i), $maxInSampleAdjust); \n";
               print F "end\n";
               print F "hData = line(domain, Yhat)\n";
      
               # Set the adjusted times on regatta$i
               $stmt = "update regatta$i set ADJAVGTIME = AVGTIME * MIN($maxInSampleAdjust, MAX($minInSampleAdjust,($theta[0] + $theta[1]*AVGTIME)))";
               $sth = $dbh->prepare($stmt);
               $sth->execute();
               $sth->finish();
            }
            else  # use non-linear curve fit
            {
               my $useCubic = 0; #try using a cubic polynomial
               
               print("Non-linear curve fit for adjustment\n");
               
               # We have the adjustments on the Y-axis in $regAdjustment[$j]
               # We have the boat speed on the X-axis in $regRegatta[$j]
               # We want to fit a non-linear curve through this data
               # --------------------------------------------------------------
               my $formula;
               if ($useCubic)
               {
                  $formula = 'a + b * x + c * x^2 + d * x^3';
               }
               else
               {
                  $formula = 'a + b * x + c * x^2';
               }
               my $variable = 'x';
  
               # We can tell the curve fit algorith where to start.  If we give a
               # good starting point it will find the solution faster
               # --------------------------------------------------------------
               my $aGuess = 1;
               my $bGuess = 0;
               my $cGuess = -1;
               my $dGuess = 0;
               
               #if ($regattas[$i] eq "Ottawa")
               #{
               #   print("OTTAWA START POINT SPECIFIED\n");
               #   $aGuess = 0.6681375387;
               #   $bGuess = 0.0075795122;
               #   $cGuess= -3.8136312388e-005;
               #   #$cGuess = -0.000038136312388;
               #}
               #if ($regattas[$i] eq "Woodstock")
               #{
               #   print("WOODSTOCK START POINT SPECIFIED\n");
               #   $aGuess = 1;
               #   $bGuess = 1;
               #   $cGuess = 1;
               #}
               my @parameters = ();
               
               if ($useCubic)
               {
                  @parameters = (
                                  # Name    Guess   Accuracy
                                  ['a',     $aGuess,    0.0000001],  # If an iteration introduces smaller
                                  ['b',     $bGuess,    0.0000001],  # changes than the accuracy, end.
                                  ['c',     $cGuess,    0.0000001],
                                  ['d',     $dGuess,    0.0000001], 
                                );
               }
               else
               {
                  @parameters = (
                                  # Name    Guess   Accuracy
                                  ['a',     $aGuess,    0.0000001],  # If an iteration introduces smaller
                                  ['b',     $bGuess,    0.0000001],  # changes than the accuracy, end.
                                  ['c',     $cGuess,    0.0000001], 
                                );
               }
                                
               my $max_iter = 1000; # maximum iterations
  
               my $square_residual = Algorithm::CurveFit->curve_fit(
                                                                    formula => $formula, 
                                                                    params  => \@parameters,
                                                                    variable=> $variable,
                                                                    xdata   => \@regRegatta,
                                                                    ydata   => \@regAdjustment,
                                                                    maximum_iterations => $max_iter,
                                                                    );
               
               print(" $parameters[0][0] $parameters[0][1] $parameters[0][2] \n");
               print(" $parameters[1][0] $parameters[1][1] $parameters[1][2] \n");
               print(" $parameters[2][0] $parameters[2][1] $parameters[2][2] \n");
               if ($useCubic)
               {
                  print(" $parameters[3][0] $parameters[3][1] $parameters[3][2] \n");
               }
               print("squared error is $square_residual\n");
               
               my $a = $parameters[0][1];
               my $b = $parameters[1][1];
               my $c = $parameters[2][1];
               my $d = 0;
               if ($useCubic)
               {
                  $d = $parameters[3][1];  # ready to comment out
               }
               
               # I seem to be getting artihmetic overflow errors and I think it's because there
               # are too many decimal points in these numbers.  Round them to 10 places
               # --------------------------------------------------------------
               my $scientificNotation;
               $scientificNotation = "";
               if ($a =~ m/(e\-\d+)/)
               {
                  $scientificNotation = $1;
               }
               if ($a =~ m/(\-?\d+\.\d{10})/)
               {
                  $a = "$1"."$scientificNotation";
               }
               $scientificNotation = "";
               if ($b =~ m/(e\-\d+)/)
               {
                  $scientificNotation = $1;
               }
               if ($b =~ m/(\-?\d+\.\d{10})/)
               {
                  $b = "$1"."$scientificNotation";
               }
               $scientificNotation = "";
               if ($c =~ m/(e\-\d+)/)
               {
                  $scientificNotation = $1;
               }
               if ($c =~ m/(\-?\d+\.\d{10})/)
               {
                  $c = "$1"."$scientificNotation";
               }
               if ($useCubic)
               {
                  $scientificNotation = "";
                  if ($d =~ m/(e\-\d+)/)
                  {
                    $scientificNotation = $1;
                  } 
                  if ($d =~ m/(\-?\d+\.\d{10})/)
                  {
                     $d = "$1"."$scientificNotation";
                  }
               }
               
               
               
               if ($useCubic)
               {
                  print("Adjustment = $a +  $b * $regattas[$i]  + $c * ($regattas[$i])^2 + $d * ($regattas[$i])^3 \n");
               }
               else
               {
                  print("Adjustment = $a +  $b * $regattas[$i]  + $c * ($regattas[$i])^2  \n"); 
               }
               print("close to 0 x coeficients indicates similar adjustment for all teams \n");
                 
               print("regattaTime, implied adjustment, adjustment used, new time \n");
               my $statY = Statistics::Descriptive::Full->new();    
               for (my $k=0; $k<$commonTeams; $k++)
               {
                  if ($useCubic)
                  {
                     $regCalcAdjustment[$k] = $a + $b*$regRegatta[$k] + $c*$regRegatta[$k]*$regRegatta[$k] + $d*$regRegatta[$k]*$regRegatta[$k]*$regRegatta[$k];                  
                  }
                  else
                  {
                     $regCalcAdjustment[$k] = $a + $b*$regRegatta[$k] + $c*$regRegatta[$k]*$regRegatta[$k];
                     
                  }
                  print("$regRegatta[$k],  $regAdjustment[$k], $regCalcAdjustment[$k], " . $regRegatta[$k]*$regCalcAdjustment[$k] . " \n");
                  $statY->add_data($regCalcAdjustment[$k]);       
               }
      
               # I observed a problem with the curve fitting results.  The teams in common between 2 regattas 
               # cover a certain interval of times say 2:00-2:35, but a one off team could have a time
               # of say 3:00 which is way outside our sample.  With the 2nd order curve fitting that
               # 3:00 team might get adjusted by too big or too small a value.  What I'm going to do is
               # clamp the adjustment between a min and max value based on the in-sample adjustments.
               # --------------------------------------------------------------
               my $maxInSampleAdjust = $statY->max();
               my $minInSampleAdjust = $statY->min();
         
               # Print MATLAB commands to log file to visualize the curve fit
               # --------------------------------------------------------------
               print F "clear X Y H domain Yhat Adjustment hData\n";               
               print F "X=${regattas[$i]}(:,1);\n";
               print F "Y=${regattas[$i]}(:,2);\n";
               print F "H=scatter(X,Y);\n";
               print F "hTitle  = title('${regattas[$i]}');\n";
               print F "domain = min(X):1:max(X)\n";
               if ($useCubic)
               {
                  print F "Adjustment = $a +  $b.*domain   + $c.*domain.*domain + $d.*domain.*domain.*domain\n";
               }
               else
               {
                  print F "Adjustment = $a +  $b.*domain   + $c.*domain.*domain \n";
               }
               print F "hold on \n";
               print F "for i=1:length(Adjustment)\n";  
               print F "   Yhat(i) = max(Adjustment(i), $minInSampleAdjust); \n";
               print F "   Yhat(i) = min(Adjustment(i), $maxInSampleAdjust); \n";
               print F "end\n";
               print F "hData = line(domain, Yhat)\n";
               
                     
               # Set the adjusted times on regatta$i
               if ($useCubic)
               {
                  $stmt = "update regatta$i set ADJAVGTIME = AVGTIME * MIN($maxInSampleAdjust, MAX($minInSampleAdjust, ($a + $b*AVGTIME + $c*AVGTIME*AVGTIME + $d*AVGTIME*AVGTIME*AVGTIME)))";
               }
               else
               {
                  $stmt = "update regatta$i set ADJAVGTIME = AVGTIME * MIN($maxInSampleAdjust, MAX($minInSampleAdjust, ($a + $b*AVGTIME + $c*AVGTIME*AVGTIME)))";
               }
               print("Adjust time via: $stmt\n");
               $sth = $dbh->prepare($stmt);
               $sth->execute();
               $sth->finish();
               
               
            }    
         }
      }
      else   # $forceZeroIntercept==1
      { 
         # This branch represents an alternative ranking where we just use how much
         # faster the average team was at a reggatta.  Here we regress 2 race
         # times against each other and force the intercept of the regression to be 
         # zero.  We then adjust based on this value.  Essentially we find for example
         # Pickering is 5% slower than Center Island on average for all the teams that
         # did both regattas.  Then we adjust all of the Pickering times to be 5%
         # faster.  This branch is also basically obsolete but I will keep the 
         # code.
         # --------------------------------------------------------------------
         print("Using average % difference method\n");
         # X - trueRank
         # Y = new regatta
         my $statX = Statistics::Descriptive::Full->new();
         my $statY = Statistics::Descriptive::Full->new();
           
         # Add data points
         for (my $j=0; $j<$commonTeams; $j++)
         {
            $statX->add_data($regTrueRank[$j]);
            $statY->add_data($regRegatta[$j]);
         }
         
         my $meanX = $statX->mean();
         my $meanY = $statY->mean();
  
         # if teams are slower on average at festival X then we need to adjust
         # the times to be faster at X
         # --------------------------------------------------------------------
         my $adjustment = $meanY / $meanX;
         
         #eg Pickering = $adjustment * CenterIsland
         print("meanX (trueRank) = $meanX   , meanY ($regattas[$i]) = $meanY \n");
         print("$regattas[$i]= trueRank * $adjustment  \n");
      
         # Set the adjusted times on regatta$i
         $stmt = "update regatta$i set ADJAVGTIME = (AVGTIME / $adjustment)";
         $sth = $dbh->prepare($stmt);
         $sth->execute();
         $sth->finish();
      }

      # Which ever method of adjustment we used above now 
      # we need to update the truerank rows for teams in common.
      # I'm going to break this into 3 SQL statements.
      # (1) increment the numRegattas in trueRank for each team being added
      # (2) set the trueRank time for teams who are being added for the first time
      # (3) set the trueRank time as the weighted avg trueRank time for teams who 
      #    are already in the trueRank
      # -----------------------------------------------------------------------
      
      # update the trueRank table to increment the number of regattas
      $stmt = "UPDATE truerank set truerank.numregattas = truerank.numregattas + 1 where exists (select regatta$i.team from regatta$i where regatta$i.team = trueRank.team)";
      $sth = $dbh->prepare($stmt);
      $sth->execute();
      $sth->finish();
      
      $stmt = "UPDATE trueRank set trueRank.time = (select regatta$i.ADJAVGTIME from regatta$i where regatta$i.TEAM=trueRank.TEAM) where trueRank.numRegattas=1 AND exists (select regatta$i.team from regatta$i where regatta$i.team = trueRank.team)";
      print("about to issue statement:\n$stmt\n");
      $sth = $dbh->prepare($stmt);
      $sth->execute();
      $sth->finish();
      
      $stmt = "UPDATE trueRank set trueRank.time = (trueRank.time*((CAST(truerank.numregattas AS FLOAT)-1.0)/CAST(truerank.numregattas AS FLOAT))) + ((1.0/CAST(truerank.numregattas AS FLOAT))*(select regatta$i.ADJAVGTIME from regatta$i where regatta$i.TEAM=trueRank.TEAM)) where trueRank.numRegattas>1 AND exists (select regatta$i.team from regatta$i where regatta$i.team = trueRank.team)";
      print("about to issue statement:\n$stmt\n");
      $sth = $dbh->prepare($stmt);
      $sth->execute();
      $sth->finish();
      
   }
   
   # Print the rankings each time we add a regatta
   #printTrueRankings();
     
}


# I finish off with a bit of a hack...
# After running the true rank program for awhile I decided it would be nice to
# see not only the overall ranking but also exaclty how a team did at each
# regatta (ie all the adjusted times).  This information is already calculated
# but for convenience I'm going to create a new table and put everything in the
# new table then print out the new table
# ----------------------------------------------------------------------------

# Create a table that has one column for each regatta
# -----------------------------------------------------------------------------
$stmt = "drop table fullResults";
$sth = $dbh->prepare($stmt);
$sth->execute(); 
$sth->finish();
 
my $creationString = "create table fullResults (TEAM VARCHAR(100), TIME DECIMAL(31,20)";
my $nullString = "NULL" . ",NULL" x $#regattas;
print("nullString is: $nullString \n");
for ($i=0; $i<=$#regattas; $i++)
{
   print("regatta $i:  $regattas[$i] \n");
   $creationString = $creationString . ", regatta$i DECIMAL(31,20) ";
}
$creationString = $creationString . ", momentum DECIMAL(6,2) )";
$stmt = $creationString;
$sth = $dbh->prepare($stmt);
$sth->execute();
$sth->finish(); 


# Populate each column of the fullResults table
# -----------------------------------------------------------------------------
print("updating time...\n");
#$stmt = "insert into fullResults (select trueRank.team, trueRank.time, $nullString from trueRank, race where trueRank.team = race.team and race.category not like 'OPEN' and exists (select  ))";
$stmt = "insert into fullResults (select trueRank.team, trueRank.time, $nullString, 0 from trueRank)";
$sth = $dbh->prepare($stmt);
$sth->execute(); 
$sth->finish(); 
   
print("updating columns...\n");
for ($i=0; $i<=$#regattas; $i++)
{
   $stmt = "UPDATE fullResults set fullResults.regatta$i = (select regatta$i.ADJAVGTIME from regatta$i where regatta$i.TEAM=fullResults.TEAM) where exists (select regatta$i.team from regatta$i where regatta$i.team = fullResults.team)";
   $sth = $dbh->prepare($stmt);
   $sth->execute(); 
   $sth->finish();   
}


# Now remove any teams we don't want based on gender
# -----------------------------------------------------------------------------
$stmt = "delete from fullResults where exists (select * from race where fullResults.team=race.team and race.category not in $gender)";
$sth = $dbh->prepare($stmt);
$sth->execute(); 
$sth->finish();


# Instead of a striaght average we decided to use a weighted average based
# on how competitive each regattas is.  First we will determine how competitive
# each regatta is and store the result in the regattas table
# ----------------------------------------------------------------------------
&computeRegattaCompetitiveness();

# Based on the competitive values calculated we will now determine the new
# weighted average time
# ----------------------------------------------------------------------------
&computeWeightedAverageTime(); 


# Print out the full table
# -----------------------------------------------------------------------------
&printFullTrueRankings();


&printMomentum();

# disconnect from database
# -----------------------------------------------------------------------------
$dbh->disconnect();

# Close MATLAB log file
# -----------------------------------------------------------------------------  
close(F);
print("Done\n");  
  
# Macro to print out the contents of the trueRank table
# -----------------------------------------------------------------------------  
sub printTrueRankings()
{
   print("TRUE RANKINGS\n");
   # Get average time for each team at each regatta
   # --------------------------------------------------------------------------
   my $stmt = "select TEAM, TIME, NUMREGATTAS from trueRank order by TIME";
   my $sth = $dbh->prepare($stmt);
   $sth->execute();
 
 
   # Get results of query into local variable
   # --------------------------------------------------------------------------
   my $col1;
   my $col2;
   my $col3; 
   my $minutes;
   my $seconds;
   my $pad;
   my $tenths;
   my $converted;
   $sth->bind_col(1,\$col1);
   $sth->bind_col(2,\$col2);
   $sth->bind_col(3,\$col3);
   my $i = 0;

   print "(Rank) Team,  Time,  # Regattas \n";
   while ($sth->fetch) 
   { 
      $i++;
      $converted = &convertTime($col2);
      #$minutes = floor($col2 / 60);
      #$seconds = $col2 % 60;
      #$tenths = &round(($col2 - floor($col2)) * 10);
      #($seconds < 10) ? ($pad="0") : ($pad="");
      #$converted = "$minutes:$pad$seconds.$tenths";
      print "($i) $col1,  $col2 ($converted),  $col3 \n"; 
      
      # TODO at this point I could look up the adjusted times in each of the regatta tables
      # that would be a lot of queries.  There is probably a better way
   }
   $sth->finish();  
}
  
  
# This macro takes a number of seconds as input and returns a string with the
# seconds formatted as "min:sec.hundredths"
# -----------------------------------------------------------------------------
sub convertTime
{
   my $col2 = shift;
   my $minutes;
   my $seconds;
   my $tenths;
   my $pad;
   
   $minutes = floor($col2 / 60);
   $seconds = $col2 % 60;
   $tenths = &round(($col2 - floor($col2)) * 10);
   if ($tenths==10)
   {
      $tenths=0;
      $seconds++;
      if ($seconds>=60)
      {
         $seconds=0;
         $minutes++;
      }
   }
   ($seconds < 10) ? ($pad="0") : ($pad="");
   
   $converted = "$minutes:$pad$seconds.$tenths";

   return $converted;
}  
  
# Perl doesn't seem to have a round() function
# -----------------------------------------------------------------------------
sub round 
{
    my($number) = shift;
    return int($number + .5 * ($number <=> 0));
}


sub max
{
   my $a = shift;
   my $b = shift;
   
   ($a > $b) ? (return $a) : (return $b);
}

sub min
{
   my $a = shift;
   my $b = shift;
   
   ($a < $b) ? (return $a) : (return $b);
}


# I hacked another function together that prints out all the columns of the 
# fullResults table.  It can handle any number of columns.  Also print out
# in HTML format to a file.
# -----------------------------------------------------------------------------
sub printFullTrueRankings()
{
   print("FULL TRUE RANKINGS \n");
 
   $htmlOut = "C:\\DragonBoatRankings\\out.html";
   if ($gender eq "('Open')")
   {
      if ($useAllDistances==1)
      {
          $htmlOut = "C:\\DragonBoatRankings\\${rankingsYear}RankingsOpen.html";
      }
      else
      {
         $htmlOut = "C:\\DragonBoatRankings\\${rankingsYear}RankingsOpen${distance}.html";
      }
   }
   elsif ($gender eq "('Mixed')")
   {
      if ($useAllDistances==1)
      {
         $htmlOut ="C:\\DragonBoatRankings\\${rankingsYear}RankingsMixed.html";
      }
      else
      {
         $htmlOut = "C:\\DragonBoatRankings\\${rankingsYear}RankingsMixed${distance}.html";
      }
      
   }
   elsif ($gender eq "('Women')")
   {
      if ($useAllDistances==1)
      {
         $htmlOut = "C:\\DragonBoatRankings\\${rankingsYear}RankingsWomens.html";
      }
      else
      {
         $htmlOut = "C:\\DragonBoatRankings\\${rankingsYear}RankingsWomens${distance}.html";
      }
   }
   
   my $priorYearFile = 0;
   
   if ($htmlOut =~ m/\\$rankingsYear(Rankings.*)/)
   {
      $priorYearFile = $1;
      $priorYearFile = $lastRankingsYear . "$priorYearFile"; 
   }
   
   
   my $outFile = "$htmlOut";
   open(F, ">$outFile") || die("Could not open $outFile");  
   
   my $stmt = "select * from fullResults order by TIME";
   my $sth = $dbh->prepare($stmt);
   $sth->execute();
 
   my $converted;
   my @col = ();
   my $colNum;
   my $arrayIndex;
   my $i = 0;
   
   # We know the first 2 columns are the team name and the trueRank time.
   # After that the number of columns is variable depending on how many
   # regattas are in the database
   # --------------------------------------------------------------------------
   $sth->bind_col(1,\$col[0]);
   $sth->bind_col(2,\$col[1]);
   print "TrueRank, Team, Overall ";
   
   # Also print results in convenient HTML format for easy uploading to
   # website
   # --------------------------------------------------------------------------
   print F "<html><head><meta http-equiv=\"Content-Type\" content=\"text/html; charset=iso-8859-1\"><style type=\"text/css\"> \n";     
   print F "<!-- \n";
   print F "body,td,th { \n";
   print F "font-family: Arial, Helvetica, sans-serif; \n";
   print F "font-size: 12px; \n";
   print F "} \n";
   print F "--> \n";
   print F "</style> \n";
   my $titleString = "${rankingsYear}"; # could set to $gender
   if ($useAllDistances)
   {
      $titleString = $titleString . " all distances ";   
   }
   else
   {
      $titleString = $titleString . " ${distance}m "; 
   }
   $titleString = $titleString . "rankings";
   
   print F "<title> $titleString </title>\n";
   print F "</head><body> \n";
   
   my @months = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);
   my @weekDays = qw(Sun Mon Tue Wed Thu Fri Sat Sun);
   my $second;
   my $minute;
   my $hour;
   my $dayOfMonth;
   my $month;
   my $yearOffset;
   my $dayOfWeek;
   my $dayOfYear;
   my $daylightSavings;
  ($second, $minute, $hour, $dayOfMonth, $month, $yearOffset, $dayOfWeek, $dayOfYear, $daylightSavings) = localtime();
  $year = 1900 + $yearOffset;
  if ($hour < 10) { $hour = "0".$hour; }
  if ($minute < 10) { $minute = "0".$minute; }
  if ($second < 10) { $second = "0".$second; }
  my $theTime = "$hour:$minute:$second, $weekDays[$dayOfWeek] $months[$month] $dayOfMonth, $year";

   if ($gender eq "('Open')")
   {
      print F "<strong> OPEN DIVISION $titleString - EASTERN CANADIAN RANKINGS  |   </strong> ";
      print F "(<a href=\"http://www.mofosdragonboat.com/Rankings/RankingsIndex.php\">Return to Canadian Dragon Boat Rankings Home</a>) |   ";
      print F "(<a href=\"http://www.mofosdragonboat.com/Rankings/$priorYearFile\">View $lastRankingsYear Rankings</a>)  <br> \n ";
      print F "Last update: $theTime\n";
      print F "<p><em>Per the International Dragon Boat Federation (IDBF) competition regulations, teams racing in the Open race division are comprised of  twenty paddlers with no age or gender restrictions.</em> \n";
   }
   elsif ($gender eq "('Mixed')")
   {
      print F "<strong> MIXED DIVISION $titleString - EASTERN CANADIAN RANKINGS  |   </strong> ";
      print F "(<a href=\"http://www.mofosdragonboat.com/Rankings/RankingsIndex.php\">Return to Canadian Dragon Boat Rankings Home</a>) |   ";
      print F "(<a href=\"http://www.mofosdragonboat.com/Rankings/$priorYearFile\">View $lastRankingsYear Rankings</a>)  <br> \n ";
      print F "Last update: $theTime\n";
      print F "<p><em>Per the International Dragon Boat Federation (IDBF) competition regulations, teams racing in the Mixed race division are comprised of  twenty paddlers, with a minimum of eight (8) and a maximum of twelve (12) Female paddlers.</em> \n";
   }
   elsif ($gender eq "('Women')")
   {
      print F "<strong> WOMENS DIVISION $titleString - EASTERN CANADIAN RANKINGS  |   </strong> ";
      print F "(<a href=\"http://www.mofosdragonboat.com/Rankings/RankingsIndex.php\">Return to Canadian Dragon Boat Rankings Home</a>) |   ";
      print F "(<a href=\"http://www.mofosdragonboat.com/Rankings/$priorYearFile\">View $lastRankingsYear Rankings</a>)  <br> \n ";
      print F "Last update: $theTime\n";
      print F "<p><em>Per the International Dragon Boat Federation (IDBF) competition regulations, teams racing in the Womens race division are comprised of twenty female paddlers</em> \n";
   }
   
   
   
   
   
   print F "<table border=\"1\">\n<tr><th>Rank</th><th>Team</th><th>Overall</th>";
   
   # The print order is potentially different than the regression order.  This is
   # probably unnecessarily complicated but just keep in mind we know the first 3
   # columns and after that the number of columns is dynamic depending on how many
   # regattas we have
   # --------------------------------------------------------------------------
   for ($j=0; $j<=$#regattas; $j++)
   {  
      $printIndex = $printOrder[$j];    
      $colNum = $j+3;
      $arrayIndex = $colNum-1;    
      $sth->bind_col($colNum,\$col[$arrayIndex]);
      print(", $regattas[$printIndex] ($competitve[$printIndex])");
      print F "<th>$regattas[$printIndex]</th>";
   }
   print("\n");
   print F "</tr>\n";
   
   
   print F "<tr bgcolor=\"FFFF00\"><th colspan=3>Regatta weight</th> \n";
   for ($j=0; $j<=$#regattas; $j++)
   {  
      $printIndex = $printOrder[$j];    
      $colNum = $j+3;
      print(", ($competitve[$printIndex])");
      print F "<th>$competitve[$printIndex]</th>";
   }
   print F "</tr>\n";
   
   $i = 0;
   my $bgColor;
   while ($sth->fetch) 
   { 
      $i++;
      ($i % 2 == 0) ? ($bgColor="AAAAFF") : ($bgColor="AAFFAA");
     
      my $printIndex = 0;
      last if (!defined($col[1])); 
      last if ($col[1] == 0) ;
      $converted = &convertTime($col[1]);
      print("$i, $col[0], $converted");
      print F "<tr bgcolor=$bgColor><td>$i</td> <td>$col[0]</td> <td>$converted</td> ";
      
      my $momentum = 0;
      my $previousTime = 0;
      for ($j=2; $j<=2+$#regattas; $j++)
      {
         # col j=2 to 2+$#regattas gives us the regress order
         # the colum containing the first regtta to print should be $printOrder[0] + 2
         $printIndex = $printOrder[$j-2];
         
         if ($previousTime == 0)
         {
         	if (defined($col[$printIndex+2]) && $col[$printIndex+2] != 0) 
         	{
               $previousTime = $col[$printIndex+2];
         	}
         }
         else
         {
            if ( defined($col[$printIndex+2]) && $col[$printIndex+2] != 0)
            {
               $momentum = $momentum + $previousTime - $col[$printIndex+2];
               $previousTime = $col[$printIndex+2];
            } 
         }
         
         $converted = "0:00.0";
         if (defined($col[$printIndex+2]))
         {
            $converted = &convertTime($col[$printIndex+2]);
         }
         if ($converted eq "0:00.0") 
         {
            print ", "; 
            print F "<td>&nbsp;</td> ";
         }
         else
         {
            print ", $converted";
            print F "<td>$converted</td> ";
         }   
      }
      $momentum = ($momentum / $col[1]) * 100;
      print(",$momentum \n");
      
      # Actually store the calculated momentum back in DB2 so we can query it
      # careful of team names with ' in them
      # -----------------------------------------------------------------------
      my $backSlashedName = $col[0];
      $backSlashedName =~ s/'/''/g;
      my $stmt2 = "UPDATE fullResults set fullResults.momentum = $momentum where fullResults.team like '$backSlashedName'";
      my $sth2 = $dbh->prepare($stmt2);
      $sth2->execute(); 
      $sth2->finish();
      
      print F "</tr>\n";
   }
   
   $sth->finish();
   print F "</html></body>\n";
   close(F);
}
 
 
 
 
 
 
 
 
 
 
 
sub printMomentum()
{
   print("Momentum Report \n");
 
   my $htmlOut = "C:\\DragonBoatRankings\\RankingsMomentum.php";  
   my $outFile = "$htmlOut";
   open(F, ">$outFile") || die("Could not open $outFile");  
   

 
   my $converted;
   my @col = ();
   my $colNum;
   my $arrayIndex;
   my $i = 0;
   
   # print results in convenient HTML format for easy uploading to
   # website
   # --------------------------------------------------------------------------
   

   print F "<?php \n";
   print F "include('rankingsHeader.php');\n";
   print F "include('rankingsFunctions.php');\n";
   print F "?>\n\n";
   
   print F "<h1>momentum watch</h1> \n";
   print F "<p><strong>Trend Analysis</strong></p>\n"; 
   print F "<p>The Momentum Watch is interested in analyzing trending throughout the season and uses " .
           "historical results to predict what teams to be on the lookout for.</p>\n";
   print F "<p>As teams train and grow together through the season and peak for different regattas, " .
           " the CDBR team looks at the percentage improvement in results to determine who the hottest " .
           "mixed teams are in Eastern Canada. </p> \n";
           
   # Divide the momentum report into groups.  Let's see which 2:00-2:05 teams have momentum,
   # which 2:05-2:10 teams have momentum, etc
   # --------------------------------------------------------------------------
   my $momentumTeams = 1;
   my $minMomentum = 0.25; # min momentum required to print even if they are top 5
   for (my $group=0; $group<=5; $group++)
   {
      my $lowerTime = 0;
      my $upperTime = 0;
      
      if ($group==0)
      {
         $lowerTime = 0;
      }
      else
      {
         $lowerTime = 120 + $group*5;
      }
      
      if ($group==5)
      {
         $upperTime = 180;
      }
      else
      {
         $upperTime = 120 + ($group+1)*5;
      }
      
      my $stmt = "select team, momentum from fullResults where time > $lowerTime and time < $upperTime and momentum>$minMomentum order by momentum desc fetch first 5 rows only";
      my $sth = $dbh->prepare($stmt);
      $sth->execute();   
      $sth->bind_col(1,\$col[0]); # team name
      $sth->bind_col(2,\$col[1]); # momentum
   
      print F "<strong> Category " . (${group}+1) . ": Race Times " . &convertTime($lowerTime) . " to " . &convertTime($upperTime) . " </strong></p> \n ";
       
      my $bgColor;
      print F "<ol start=\"$momentumTeams\"> \n";
      while ($sth->fetch) 
      { 
         $momentumTeams++;
         print F "<li>$col[0]</li>\n";
      }
      print F "</ol> \n";
      print F "<p>&nbsp<p>\n";
   
      $sth->finish();
   }
   
   print F "<p>&nbsp<p>\n";
   print F "<?php \n";
   print F "include('rankingsFooter.php'); \n";
   print F "?> \n";
   
   close(F);
}
 


###############################################################################
# Select first 100 mixed teams ordered by trueRank time
# for each regatta if the team is there add 100-$i points to competitive factor
# print competitive factor.  Decide on a tiered system converting to 0.25,0.5,0.75,1
###############################################################################
sub computeRegattaCompetitiveness()
{
   my @row = ();
   my $i = 0;
   my $offset = 2;
   my @totalCompetitiveness = ();
   my $competitivePoints = 51;
   my $team = "";
   my $found = 0;
   
   my $stmt = "select fullResults.team from fullResults order by TIME fetch first 50 rows only";
   my $sth = $dbh->prepare($stmt);
   $sth->execute();
   while (@row = $sth->fetchrow_array) 
   {
      #row[0] contains team name

      # For each regatta check if this team was in attendance.  If the team was in
      # attendance then award the regatta the appropriate number of competitive points
      # -----------------------------------------------------------------------
      for ($i=0; $i<=$#regattas; $i++)
      {
         $found = 0;
         $team = $row[0];
         $team =~ s/'/''/g;
         #print("team is $team, regatta is  $regattas[$i] \n");       
         my $stmt2 = "select count(*) from race where team like '$team' and regatta like '$regattas[$i]'";
         my $sth2 = $dbh->prepare($stmt2);
         $sth2->execute(); 
         $sth2->bind_col(1,\$found); 
         $sth2->fetch;     
         #print("debug: found $team in $regattas[$i] ? ---> $found \n");
         if ($found >= 1)
         {
            #$totalCompetitiveness[$i] += ($competitivePoints/4) * ($competitivePoints/4) * ($competitivePoints/4) * ($competitivePoints/4);
            $totalCompetitiveness[$i] += $competitivePoints * $competitivePoints;  
         }
         $sth2->finish();
      }
      
      # Now move on to next iteration of loop.  The next team is slightly less competitive
      # and will give 1 less competitive points to the regatta
      # -----------------------------------------------------------------------
      $competitivePoints--;
   }   
   $sth->finish(); 

   # At this point the total competitiveness for each regatta is stored in the
   # $totalCompetitiveness[$i] array.  This could be as high as 5000+.  We will
   # divide each element through by the base (center island) element 
   # -------------------------------------------------------------------------
   my $normalizer = $totalCompetitiveness[0];
   if ($normalizer > 0)
   {
      for ($i=0; $i<=$#regattas; $i++)
      {
         print("$regattas[$i] non normalized competitiveness: $totalCompetitiveness[$i] \n");
         $totalCompetitiveness[$i] = $totalCompetitiveness[$i] / $normalizer;
         $totalCompetitiveness[$i] = &min($totalCompetitiveness[$i], 1.5);
         $totalCompetitiveness[$i] = &max($totalCompetitiveness[$i], 0.1);
         $totalCompetitiveness[$i] = &round(10 * ($totalCompetitiveness[$i] + 0.0499)) / 10;
      }
   }
   else
   {
      print("Problem with competitiveness calculations\n");
   } 
   
   # Now update the regattas table with the new competitiveness value we
   # calculated.
   # --------------------------------------------------------------------------
   for ($i=0; $i<=$#regattas; $i++)
   {
      print("$regattas[$i] competitiveness: $totalCompetitiveness[$i] \n");
      $competitve[$i] = &round(100 * $totalCompetitiveness[$i]) / 100;
      $stmt = "UPDATE regattas set regattas.COMPETITIVE = $totalCompetitiveness[$i] where regattas.NAME like '$regattas[$i]'";
      $sth = $dbh->prepare($stmt);
      $sth->execute();
      $sth->finish();
   } 
 
 
}

###############################################################################
# This function will look at all the regattas a team did, and look up how competitive
# each regatta was, and then compute the weighted average based on competitive level.
# For example if a team did Center Island which has a competitive level of 1.0 and
# Milton which has a competitive level of 0.25 then it will calculate 
# CItime * 1.0/1.25 + MiltonTime*0.25/1.25
###############################################################################
sub computeWeightedAverageTime()
{
   my @row = ();
   my $i = 0;
   my $offset = 2;
   my $numerator = 0;
   my $denominator = 0;
   my $team = "";
   my $weightedAverage = 0;
   my $stmt = "select * from fullResults order by TIME";
   my $sth = $dbh->prepare($stmt);
   $sth->execute();
   
   while (@row = $sth->fetchrow_array) 
   {
      #row[0] contains team name
      #row[1] contains non weighted average
      #row[2] to row[2+$#numRegattas] contains the time recorded at each regatta
     
      # calculate the weighted average
      $numerator = 0;
      $denominator = 0;
      for ($i=0; $i<=$#regattas; $i++)
      #for ($i=0; $i<$#regattas; $i++)
      {
      	if (defined($row[$i+$offset]))
      	{
          $numerator = $numerator + ($row[$i+$offset] * $competitve[$i]);
          $denominator = $denominator + $competitve[$i] if ($row[$i+$offset] > 0);
      	}
      }
      next if ($denominator <= 0);
      $weightedAverage = $numerator / $denominator;
      
      # update the average
      my $backSlashedName = $row[0];
      $backSlashedName =~ s/'/''/g;
      my $stmt2 = "UPDATE fullResults set fullResults.time = $weightedAverage where fullResults.team like '$backSlashedName'";
      my $sth2 = $dbh->prepare($stmt2);
      $sth2->execute(); 
      $sth2->finish();
   }   
   $sth->finish();  
    
}  